﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;
using Trying1;

namespace Trying1.Classes
{
    class StaffDBManager
    {
        public static ArrayList GetAll()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Staff";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Staff m = new Staff();
                    m.staffNo = (int)dr["staffNo"];
                    m.staffName = (string)dr["staffName"];
                    m.DOB = (DateTime)dr["DOB"];
                    m.AccountNo = (string)dr["AccountNo"];
                    m.Address = (string)dr["Address"];
                    m.PhoneNum = (int)dr["PhoneNum"];
                    m.DutyType = (string)dr["DutyType"];
                    m.StaffType = (string)dr["StaffType"];
                    m.username = (string)dr["username"];
                    m.password = (string)dr["password"];
                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static Staff GetStaffByUsername(string username)
        {
            Staff m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff WHERE username=@username";
                comm.Parameters.AddWithValue("@username", username);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new Staff();
                    m.staffNo = (int)dr["staffNo"];
                    m.staffName = (string)dr["staffName"];
                    m.DOB = (DateTime)dr["DOB"];
                    m.AccountNo = (string)dr["AccountNo"];
                    m.Address = (string)dr["Address"];
                    m.PhoneNum = (int)dr["PhoneNum"];
                    m.DutyType = (string)dr["DutyType"];
                    m.StaffType = (string)dr["StaffType"];
                    m.username = (string)dr["username"];
                    m.password = (string)dr["password"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }

        public static Staff GetStaffByStaffName(string username)
        {
            Staff m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff WHERE staffname=@staffname";
                comm.Parameters.AddWithValue("@staffname", username);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new Staff();
                    m.staffNo = (int)dr["staffNo"];
                    m.staffName = (string)dr["staffName"];
                    m.DOB = (DateTime)dr["DOB"];
                    m.AccountNo = (string)dr["AccountNo"];
                    m.Address = (string)dr["Address"];
                    m.PhoneNum = (int)dr["PhoneNum"];
                    m.DutyType = (string)dr["DutyType"];
                    m.StaffType = (string)dr["StaffType"];
                    m.username = (string)dr["username"];
                    m.password = (string)dr["password"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }

        public static Staff GetStaffBystaffNo(int staffNo)
        {
            Staff m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff WHERE staffNo=@staffNo";
                comm.Parameters.AddWithValue("@staffNo", staffNo);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new Staff();
                    m.staffNo = (int)dr["staffNo"];
                    m.username = (string)dr["username"];
                    m.password = (string)dr["password"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }
        public static int InsertStaff(Staff m)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Staff(staffName,DOB,AccountNo,Address,PhoneNum,DutyType,StaffType,username,password)" +
                    " VALUES (@staffName,@DOB,@AccountNo,@Address,@PhoneNum,@DutyType,@StaffType,@username,@password)";
                comm.Parameters.AddWithValue("@staffName", m.staffName);
                comm.Parameters.AddWithValue("@DOB", m.DOB);
                comm.Parameters.AddWithValue("@AccountNo", m.AccountNo);
                comm.Parameters.AddWithValue("@Address", m.Address);
                comm.Parameters.AddWithValue("@PhoneNum", m.PhoneNum);
                comm.Parameters.AddWithValue("@DutyType", m.DutyType);
                comm.Parameters.AddWithValue("@StaffType", m.StaffType);
                comm.Parameters.AddWithValue("@username", m.username);
                comm.Parameters.AddWithValue("@password", m.password);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }
        public static int UpdateStaff(Staff m)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Staff SET staffName=@staffName,DOB=@DOB,AccountNo=@AccountNo, Address=@Address, PhoneNum=@PhoneNum, DutyType=@DutyType,StaffType=@StaffType where staffNo=@staffNo";
                comm.Parameters.AddWithValue("@staffNo", m.staffNo);
                comm.Parameters.AddWithValue("@staffName", m.staffName);
                comm.Parameters.AddWithValue("@DOB", m.DOB);
                comm.Parameters.AddWithValue("@AccountNo", m.AccountNo);
                comm.Parameters.AddWithValue("@Address", m.Address);
                comm.Parameters.AddWithValue("@PhoneNum", m.PhoneNum);
                comm.Parameters.AddWithValue("@DutyType", m.DutyType);
                comm.Parameters.AddWithValue("@StaffType", m.StaffType);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int UpdateStaffAccount(Staff m)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Staff SET username=@username,password=@password where staffNo=@staffNo";
                comm.Parameters.AddWithValue("@staffNo", m.staffNo);
                comm.Parameters.AddWithValue("@username", m.username);
                comm.Parameters.AddWithValue("@password", m.password);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int DeleteStaff(string staffName)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Delete from staff where staffName=@staffName";
                comm.Parameters.AddWithValue("@staffName", staffName);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }
    }
}
